package com.rivues.module.datamodel.web.handler;


import java.io.UnsupportedEncodingException;
import java.net.ConnectException;
import java.net.URLEncoder;
import java.security.NoSuchAlgorithmException;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.validation.Valid;



import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;

import org.rivu.handler.IDaoManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import antlr.StringUtils;

import com.alibaba.druid.pool.DruidDataSource;
import com.rivues.core.RivuDataContext;
import com.rivues.core.RivuDataContext.ConnectionTypeEnum;
import com.rivues.module.platform.web.handler.Handler;
import com.rivues.module.platform.web.handler.RequestData;
import com.rivues.module.platform.web.handler.ResponseData;
import com.rivues.module.platform.web.interceptor.LogIntercreptorHandler;
import com.rivues.module.platform.web.model.Cube;
import com.rivues.module.platform.web.model.CubeLevel;
import com.rivues.module.platform.web.model.CubeMeasure;
import com.rivues.module.platform.web.model.CubeMetadata;
import com.rivues.module.platform.web.model.Database;
import com.rivues.module.platform.web.model.Dimension;
import com.rivues.module.platform.web.model.JobDetail;
import com.rivues.module.platform.web.model.Organ;
import com.rivues.module.platform.web.model.QueryLog;
import com.rivues.module.platform.web.model.TableDir;
import com.rivues.module.platform.web.model.TableProperties;
import com.rivues.module.platform.web.model.TableTask;
import com.rivues.module.platform.web.model.TypeCategory;
import com.rivues.module.platform.web.model.User;
import com.rivues.module.platform.web.model.UserOrgan;
import com.rivues.util.RivuTools;
import com.rivues.util.data.DatabaseMetaDataHandler;
import com.rivues.util.datasource.DataSourceTools;
import com.rivues.util.local.LocalTools;
import com.rivues.util.serialize.JSON;
import com.rivues.util.tools.H2Helper;
import com.rivues.util.tools.RivuColumnMetadata;
import com.rivues.util.tools.RivuTableMetaData;
  
@Controller  
@RequestMapping("/{orgi}/dm/database")  
public class DatabaseController extends Handler{  
	private static final String String = null;
	private final Logger log = LoggerFactory.getLogger(LogIntercreptorHandler.class); 
    @RequestMapping(value="/index" , name="index" , type="dm",subtype="database")
    public ModelAndView index(HttpServletRequest request , @PathVariable String orgi) throws Exception{  
    	ModelAndView view = request(super.createDMTempletResponse("/pages/datamodel/database/index") , orgi) ;
    	return createModelAndView(null , "0", null , orgi , view) ;
    }
    
    @RequestMapping(value="/{dbid}/tabledir/{tabledirid}" , name="tabledir" , type="dm",subtype="database")
    public ModelAndView tabledir(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid, @PathVariable String tabledirid,@Valid String ation_message) throws Exception{  
    	ModelAndView view = request(super.createDMTempletResponse("/pages/datamodel/database/metadatalist") , orgi) ;
    	if(ation_message != null){
    		ation_message = ation_message.replaceAll("'", "").replace("\n", "");
    	}
    	view.addObject("ation_message",ation_message);
    	return createModelAndView(dbid , tabledirid , tabledirid  , orgi , view) ;
    }
    
    /**
     * 查看数据库详细信息
     * @param request
     * @param orgi
     * @param dbid
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/{dbid}/database" , name="database" , type="dm",subtype="database")
    public ModelAndView database(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid) throws Exception{  
    	ModelAndView view = request(super.createDMTempletResponse("/pages/datamodel/database/index") , orgi) ;
    	return createModelAndView(dbid , "0" , dbid , orgi , view) ;
    }
    
    /**
     * 查看数据表详细信息
     * @param request
     * @param orgi
     * @param dbid
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/{dbid}/{parentid}/table/{tableid}" , name="table" , type="dm",subtype="database")
    public ModelAndView table(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid, @PathVariable String parentid, @PathVariable String tableid) throws Exception{  
    	ModelAndView view = request(super.createDMTempletResponse("/pages/datamodel/database/table") , orgi) ;
    	TableTask tableTask = (TableTask) super.getService().getIObjectByPK(TableTask.class, tableid);
    	view.addObject("table", tableTask) ;
    	view.addObject("tablepropertyList", super.getService().findAllByCriteria(DetachedCriteria.forClass(TableProperties.class).add(Restrictions.eq("orgi", orgi)).add(Restrictions.eq("dbtableid", tableid)))) ;
    	HttpSession session = request.getSession();
    	session.setAttribute("cuTable", tableTask);
    	return createModelAndView(dbid , parentid , parentid , orgi , view) ;
    }
    
    /**
     * 获取 基本数据信息
     * @param dbid
     * @param tabledirid
     * @param orgi
     * @param view
     * @return
     * @throws SQLException 
     * @throws NoSuchAlgorithmException 
     */
    private ModelAndView createModelAndView(String dbid , String tabledirid ,String curdataid , String orgi , ModelAndView view ) throws NoSuchAlgorithmException, SQLException{
    	List<Database> databaseList = super.getService().findAllByCriteria(DetachedCriteria.forClass(Database.class).add(Restrictions.eq("orgi", orgi))) ;
    	Database database = null ;
    	if(databaseList!=null && databaseList.size()>0 && dbid==null){
    		database = databaseList.get(0) ;
    		curdataid = dbid = database.getId();
    	}else if(databaseList!=null && databaseList.size()>0){
    		for(Database db : databaseList){
    			if(dbid.equals(db.getId())){
    				database = db ;
    				break ;
    			}
    		}
    	}
    	
    	//首次进入，dbid为空
    	if(dbid != null){
	    	//获取当前链接池信息
    		if(RivuDataContext.ConnectionTypeEnum.JDBC.toString().equalsIgnoreCase(database.getConnctiontype()) ){
    			Map<String,Object> datasourceMap = DataSourceTools.getDataSourceStat(database!=null && RivuDataContext.R3_SYSTEM.toString().equals(database.getName()) ? RivuDataContext.R3_SYSTEM : dbid);
    	    	if(null!=datasourceMap && datasourceMap.size()>0){
    	    		view.addObject("datasourceMap", datasourceMap) ;
    	    	}
    		}
	    	
	    	
	    	view.addObject("database", database) ;
	    	view.addObject("databaseList", databaseList) ;
	    	view.addObject("dbid", dbid) ;
	    	view.addObject("parentid", tabledirid) ;
	    	view.addObject("curdataid", curdataid) ;
	    	view.addObject("tabledirList", super.getService().findAllByCriteria(DetachedCriteria.forClass(TableDir.class).add(Restrictions.eq("orgi", orgi)).add(Restrictions.eq("type", RivuDataContext.TableDirType.METADATA.toString())))) ;
	    	view.addObject("tableList", super.getService().findAllByCriteria(DetachedCriteria.forClass(TableTask.class).add(Restrictions.eq("orgi", orgi)).add(Restrictions.eq("dbid", dbid)).add(Restrictions.eq("tabledirid", tabledirid)))) ;
    	}
    	return view ;
    }
   /**
    * 测试数据链接是否成功
    * @param request
    * @param orgi
    * @param id
    * @return
	* @throws NoSuchAlgorithmException 
	* @throws SQLException 
	* @throws Exception 
    */
    @RequestMapping(value = "/databasetest/{id}",name="databasetest" , type="dm",subtype="database")
    public ModelAndView databasetest(HttpServletRequest request,@PathVariable String orgi, @PathVariable String id) throws NoSuchAlgorithmException, SQLException{

    	ResponseData responseData = new ResponseData("/pages/datamodel/database/loadurl_result" , orgi) ;
    	String result = LocalTools.getMessage("S_DM_80020001");
    	Database database =  (Database) super.getService().getIObjectByPK(Database.class, id);
    	DruidDataSource ds = DataSourceTools.getDataSource(database.getId());
    	try {
			 if(!DatabaseMetaDataHandler.testConnection(database)){
				 result = LocalTools.getMessage("E_DM_80020001");
			 }else if(ds==null){
				 DataSourceTools.createDataSource(database); 
			 }
    	}catch (Exception e) {
    		e.printStackTrace();
			ModelAndView view = request(responseData,orgi) ;
			String erMs = LocalTools.getMessage("E_DM_80020002");
			String err = RivuTools.getExceptionMessage(e);
			int errint = err.length();
			String newErroString = "";
			if(errint > 100){
				if(err.indexOf(":") > 0){
					newErroString = err.substring(0,err.indexOf(")")+1);
				}else{
					newErroString = err;
				}
			}else{
				newErroString = err;
			}
			result = erMs+err;
			if(ds!=null){
				ds.close();
			}
	    	view.addObject("result_msg",erMs);
	    	view.addObject("err",newErroString.replaceAll("'", ""));
	    	return view;
		}
    	ModelAndView view = request(responseData,orgi) ;
    	view.addObject("result_msg",result);
    	return view;
    }
    
    /**
     * 数据模型查询
     * @param request
     * @param orgi
     * @param dbid
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/databasecube/{dbid}" , name="databasecube" , type="dm",subtype="cube")
    public ModelAndView databasecube(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid){
    	ModelAndView view = request(super.createDMTempletResponse("/pages/datamodel/database/databasecube") , orgi) ;
    	//获取到db目录
    	List<Database> databaseList = super.getService().findAllByCriteria(DetachedCriteria.forClass(Database.class).add(Restrictions.eq("orgi", orgi))) ;
    	view.addObject("databaseList", databaseList) ;
    	//获取到当前db
    	Database database =  (Database) super.getService().getIObjectByPK(Database.class, dbid);
    	view.addObject("database", database);
    	//通过dbid查询到对应db的数据模型
    	List<Cube> cubes = super.getService().findAllByCriteria(DetachedCriteria.forClass(Cube.class).add(Restrictions.eq("db", dbid)).add(Restrictions.eq("orgi", orgi))); 
    	view.addObject("cubes", cubes) ;
    	view.addObject("dbid", dbid) ;
    	return view;
    }
    
    /**
     * 重置连接池
     * @param request
     * @param orgi
     * @param dbid
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/databaseeditdo/{dbid}" , name="databaseeditdo" , type="dm",subtype="database")
    public ModelAndView databaseeditdo(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid) throws Exception{  
    	ResponseData responseData = new ResponseData("/pages/datamodel/database/loadurl_result" , orgi) ;
    	String result = LocalTools.getMessage("S_DM_80020002");
    	ModelAndView view = request(responseData,orgi) ;
    	DruidDataSource ds = DataSourceTools.getDataSource(dbid);
    	if(ds!=null){
    		ds.close();
    	}
    	Database database =  (Database) super.getService().getIObjectByPK(Database.class, dbid);
    	
    	try {
			 if(!DatabaseMetaDataHandler.testConnection(database)){
				 result = LocalTools.getMessage("E_DM_80020001");
			 }else if(DataSourceTools.getDataSource(database.getId())==null){
				 DataSourceTools.createDataSource(database); 
			 }
		} catch (Exception e) {
			String erMs = LocalTools.getMessage("E_DM_80020002");
			String err = (null!=e.getMessage()) ? e.getMessage() : e.getCause().getMessage();
			//去除字符串中的空格
			Pattern p = Pattern.compile("\\s*");
			Matcher m = p.matcher(err);
			String newErroString = m.replaceAll("");
			result = erMs+newErroString;
		
		}
    	view.addObject("result_msg",result);
    	return view;
    }

    /**
     * 新增数据库
     * @param request
     * @param orgi
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/databaseadd" ,name="databaseadd" , type="dm",subtype="database")
    public ModelAndView databaseadd(HttpServletRequest request , @PathVariable String orgi) throws Exception{  
    	return request(new ResponseData("/pages/datamodel/database/databaseadd") , orgi) ;
    }
    
    @RequestMapping(value="/databaseaddo", method=RequestMethod.POST,name="databaseaddo" , type="dm",subtype="database")  
    public ModelAndView databaseaddo(HttpServletRequest request , @PathVariable String orgi, @Valid Database db) throws NoSuchAlgorithmException{  
    	ResponseData responseData = new ResponseData("redirect:/{orgi}/dm/database/index.html?msgcode=S_DM_10010028");
    	String type = db.getConnctiontype();
    	if (!"".equals(type) && null != type) {
    		int count;
    		if ("jdbc".equals(type)) {
    			 count  = super.getService().getCountByCriteria(DetachedCriteria.forClass(Database.class).add(Restrictions.eq("connctiontype","jdbc")).add(Restrictions.eq("name", db.getName())).add(Restrictions.eq("orgi", orgi)));
    			if(count>0){
    	    		responseData = new ResponseData("redirect:/{orgi}/dm/database/index.html?msgcode=E_DM_10010028");
    			}else{
    				String passWord = db.getPassword();
    	        	if (null != passWord && !"".equals(passWord) && !"null".equals(passWord)){
    	        		db.setPassword(RivuTools.encryption(passWord));
    	        	}
    	        	db.setOrgi(orgi);
    	        	db.setCreatetime(new Date());
    	        	db.setCreateuser(super.getUser(request).getId());
    	        	db.setSqldialect(db.getDatabasetype());
    	        	super.getService().saveIObject(db);
    			}
    		}else if("jndi".equals(type)){
    			 count  = super.getService().getCountByCriteria(DetachedCriteria.forClass(Database.class).add(Restrictions.eq("connctiontype","jndi")).add(Restrictions.eq("jndiname", db.getJndiname())).add(Restrictions.eq("orgi", orgi)));
    			if(count>0){
    	    		responseData = new ResponseData("redirect:/{orgi}/dm/database/index.html?msgcode=E_DM_10010028");
    			}else{
    				db.setOrgi(orgi);
    	        	db.setCreatetime(new Date());
    	        	db.setCreateuser(super.getUser(request).getId());
    	        	db.setSqldialect(db.getDatabasetype());
    	        	super.getService().saveIObject(db);
    			}
    		}
    	}
  
        return request(responseData , orgi) ;
    } 
    /**
     * 修改数据库
     * @param request
     * @param orgi
     * @param dbid
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/databaseedit/{dbid}" , name="databaseedit" , type="dm",subtype="database")
    public ModelAndView databaseedit(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid) throws Exception{  
    	ModelAndView view =  request(new ResponseData("/pages/datamodel/database/databaseedit") , orgi) ;
    	view.addObject("database", dbid) ;
    	Database datebase = (Database) super.getService().getIObjectByPK(Database.class, dbid);
    	view.addObject("data",datebase) ;
    	return view ;
    }
    @RequestMapping(value="/databaseedito", method=RequestMethod.POST,name="databaseedito" , type="dm",subtype="database")
    public ModelAndView databaseedito(HttpServletRequest request , @PathVariable String orgi ,@Valid Database db ) throws Exception{  
    	ResponseData responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(db.getId()).append("/database.html?msgcode=S_DM_10010029").toString()) ;
    	
    	String type = db.getConnctiontype();
    	String cuId = db.getId();//当前ID
    	if (!"".equals(type) && null != type) {
    		List<Database> list = new ArrayList<Database>();
    		if ("jdbc".equals(type)) {
    			list = super.getService().findAllByCriteria(DetachedCriteria.forClass(Database.class).add(Restrictions.eq("name",db.getName())).add(Restrictions.eq("connctiontype","jdbc")).add(Restrictions.eq("orgi", orgi)));
    			String passWord = db.getPassword();
            	if (null != passWord && ! "".equals(passWord) && !"null".equals(passWord)){
            		db.setPassword(RivuTools.encryption(passWord));
            	}
    		} else if ("jndi".equals(type)) {
    			list = super.getService().findAllByCriteria(DetachedCriteria.forClass(Database.class).add(Restrictions.eq("jndiname",db.getJndiname())).add(Restrictions.eq("connctiontype","jndi")).add(Restrictions.eq("orgi", orgi)));
    		}
    		//check
    		if (null == list || list.size() <= 0) {
    			db.setSqldialect(db.getDatabasetype());
    			super.getService().saveOrUpdateIObject(db);
    		} else {
    			if (list.size() == 1) {
    				if (cuId.equals(list.get(0).getId())){
    					db.setSqldialect(db.getDatabasetype());
    					super.getService().saveOrUpdateIObject(db);
    				} else {
    					responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(db.getId()).append("/database.html?msgcode=E_DM_10010030").toString()) ;
    				}
    			} else {
    				responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(db.getId()).append("/database.html?msgcode=E_DM_10010030").toString()) ;
    			}
    		}
    	}
    	 return request(responseData , orgi) ;
    }
    /**
     * 删除数据库
     * @param request
     * @param orgi
     * @param organ
     * @return 
     */
    
    @RequestMapping(value="/databasedelo/{id}", name="databasedelo" , type="dm",subtype="database")
    public ModelAndView databasedelo(HttpServletRequest request,@PathVariable String orgi,@PathVariable String id){
    	ResponseData responseData = new ResponseData("redirect:/{orgi}/dm/database/index.html");
    	Database db = (Database)super.getService().getIObjectByPK(Database.class, id);
    	int count = super.getService().getCountByCriteria(DetachedCriteria.forClass(TableTask.class).add(Restrictions.eq("dbid", id)).add(Restrictions.eq("orgi", orgi)));
    	int count1= super.getService().getCountByCriteria(DetachedCriteria.forClass(Cube.class).add(Restrictions.eq("db", id)).add(Restrictions.eq("orgi", orgi)));
    	if(count>0||count1>0){
    		responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(db.getId()).append("/database.html?msgcode=E_DM_10010036").toString()) ; 
    	}else{
        	super.getService().deleteIObject(db);
        	responseData.setPage("redirect:/{orgi}/dm/database/index.html?msgcode=S_DM_10010001");
    	}
   
    	ModelAndView modelView = request(responseData , orgi);
        return modelView ;
    }
    /**
     * 增加	数据库表的逻辑结构目录
     * @param request
     * @param orgi
     * @param dbid
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/{dbid}/tablediradd/{parentid}" , name="tablediradd" , type="dm",subtype="database")
    public ModelAndView tablediradd(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid , @PathVariable String parentid) throws Exception{  
    	ModelAndView view =  request(new ResponseData("/pages/datamodel/database/tablediradd") , orgi) ;
    	view.addObject("databaseid", dbid) ;
    	view.addObject("parentid", parentid) ;
    	return view ;
    }

    @RequestMapping(value="/{dbid}/tablediraddo" , method=RequestMethod.POST,name="tablediraddo" , type="dm",subtype="database")
    public ModelAndView tablediraddo(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid , @Valid TableDir tabledir) throws Exception{  
    	ResponseData responseData = new ResponseData("redirect:/{orgi}/dm/database/index.html");
    	if(tabledir.getName().equals("默认目录") || tabledir.getName().equals("结算数据")){
    		responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/{dbid}/tabledir/").append(tabledir.getParentid()).append(".html?msgcode=E_DM_10010027").toString()) ;
    		return request(responseData,orgi);
    	}
    	int count = super.getService().getCountByCriteria(DetachedCriteria.forClass(TableDir.class).add(Restrictions.eq("databaseid",dbid)).add(Restrictions.eq("parentid",tabledir.getParentid())).add(Restrictions.eq("name", tabledir.getName())).add(Restrictions.eq("orgi", orgi)));
    	if(count==0){
    		tabledir.setOrgi(orgi) ;
	    	tabledir.setType(RivuDataContext.TableDirType.METADATA.toString()) ;
	    	tabledir.setCreater(super.getUser(request).getId()) ;
	    	tabledir.setCreatetime(new Date()) ;
	    	tabledir.setUpdatetime(new Date()) ;
	    	super.getService().saveIObject(tabledir) ;
	    	responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/{dbid}/tabledir/").append(tabledir.getId()).append(".html?msgcode=S_DM_10010027").toString()) ;
    	}else{
    		responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/{dbid}/tabledir/").append(tabledir.getParentid()).append(".html?msgcode=E_DM_10010027").toString()) ;
    	}
    	return request(responseData,orgi);
    }
    /**
     * 修改数据库表的逻辑结构目录
     * @param request
     * @param orgi
     * @param dbid
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/{dbid}/tablediredit/{parentid}" , name="tablediredit" , type="dm",subtype="database")
    public ModelAndView tablediredit(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid , @PathVariable String parentid) throws Exception{  
    	ModelAndView view =  request(new ResponseData("/pages/datamodel/database/tablediredit") , orgi) ;
    	view.addObject("dbid", dbid) ;
    	view.addObject("parentid", parentid) ;
    	view.addObject("tabledir", super.getService().getIObjectByPK(TableDir.class, parentid)) ;
    	return view ;
    }
    /**
     * 修改数据库表的逻辑结构目录
     * @param request
     * @param orgi
     * @param tabledir
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/tablediredito", method=RequestMethod.POST,name="tablediredito" , type="dm",subtype="database" )  
    public ModelAndView tablediredito(HttpServletRequest request , @PathVariable String orgi,@Valid TableDir tabledir,@Valid String id,@Valid String databaseid,@Valid String parentid){  
    	TableDir dir = (TableDir)super.getService().getIObjectByPK(TableDir.class, tabledir.getId());
    	ResponseData responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(databaseid).append("/tabledir/").append(id).append(".html?msgcode=S_DM_10010025").toString()) ;
    	if(tabledir.getName().equals("默认目录") || tabledir.getName().equals("结算数据")){
    		responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(tabledir.getDatabaseid()).append("/tabledir/").append(tabledir.getId()).append(".html?msgcode=E_DM_10010025").toString());
    		return request(responseData,orgi);
    	}
    	int count = super.getService().getCountByCriteria(DetachedCriteria.forClass(TableDir.class).add(Restrictions.eq("databaseid",databaseid)).add(Restrictions.eq("parentid",tabledir.getParentid())).add(Restrictions.eq("name", tabledir.getName())).add(Restrictions.eq("orgi", orgi)));
    	if(!dir.getName().equals(tabledir.getName())&&count>0){
    		responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(tabledir.getDatabaseid()).append("/tabledir/").append(tabledir.getId()).append(".html?msgcode=E_DM_10010025").toString());
    	}else{
    		dir.setName(tabledir.getName());
        	dir.setUpdatetime(new Date());
        	super.getService().saveOrUpdateIObject(dir) ;
    	}
    	return request(responseData,orgi);
    } 
    /**
     * 删除目录
     * @param request
     * @param orgi
     * @param organ
     * @return 
     */
    @RequestMapping(value="/tabledirdelo/{id}",name="tabledirdelo" , type="dm",subtype="database" )
    public ModelAndView tabledirdelo(HttpServletRequest request,@PathVariable String orgi,@PathVariable String id){
    	TableDir dir = (TableDir)super.getService().getIObjectByPK(TableDir.class, id);
    	ResponseData responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dir.getDatabaseid()).append("/tabledir/").append(dir.getParentid()).append(".html?msgcode=S_DM_10010000").toString()) ;
    	int count = super.getService().getCountByCriteria(DetachedCriteria.forClass(TableTask.class).add(Restrictions.eq("tabledirid", id)).add(Restrictions.eq("orgi", orgi)));
    	if(count>0){
    		responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dir.getDatabaseid()).append("/tabledir/").append(id).append(".html?msgcode=E_DM_10010037").toString()) ;
    	}else if (null != dir) {
    		String pid =  dir.getId();
        	List<TableDir> tlist = super.getService().findAllByCriteria(
        			DetachedCriteria.forClass(TableDir.class).add(Restrictions.eq("parentid",pid))
        					);
        	if (null != tlist && tlist.size() > 0) {//有子目录的提示
        		responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dir.getDatabaseid()).append("/tabledir/").append(id).append(".html?msgcode=E_DM_10010038").toString()) ;
        }else {//没有子目录的，删除掉
        		super.getService().deleteIObject(dir);
        	}
    	}
    	ModelAndView modelView = request(responseData , orgi);
        return modelView ;
    	
    }
    /**
     * 查询数据表
     * @param request
     * @param orgi
     * @param dbid
     * @return
     */
    @RequestMapping(value = "/{databaseid}/{dirid}/selecttable",name="selecttable" , type="dm",subtype="database")
    public ModelAndView seclectTable(HttpServletRequest request ,@PathVariable String orgi, @PathVariable String databaseid, @PathVariable String dirid,@Valid String tablename) throws Exception{
    	ResponseData responseData = new ResponseData("/pages/datamodel/database/tablelist" ) ; 
    	Database database = (Database) super.getService().getIObjectByPK(Database.class, databaseid) ;
		ModelAndView view = request(responseData,orgi);
		//此处取出用户输入限制符 由于查询语句用sql语句拼写而成需要进行字符的转义!
		StringBuilder tableNamePattern =new StringBuilder().append("%").append(tablename).append("%");
		view.addObject("tables",DatabaseMetaDataHandler.getTables(database,tableNamePattern.toString()));
		return view;
    }
    
    
    /**
     * 导入数据表
     * @param request
     * @param orgi
     * @param dbid
     * @return
     * @throws Exception
     */  
    @RequestMapping(value="/{dbid}/tableadd/{dirid}" , name="tableadd" , type="dm",subtype="database")
    public ModelAndView tableadd(HttpServletRequest request , @PathVariable String orgi , @PathVariable String dbid, @PathVariable String dirid) throws Exception{  
    	ModelAndView view1 =  request(new ResponseData("/pages/datamodel/database/tableadd") , orgi) ;
    	Database database = (Database) super.getService().getIObjectByPK(Database.class, dbid) ;
    	
    	ResponseData response = new ResponseData("/pages/public/success",orgi) ;
    	ModelAndView view = request(response,orgi);
    	try{
    		//加载出数据库连接中的表
    		view.addObject("tables", DatabaseMetaDataHandler.getTables(database,null));
    	}catch (Exception e) {
    		//当数据库连接不成功时，给出错误消息
    		e.printStackTrace();
 			String result = LocalTools.getMessage("E_DM_80010039")+e;
 			view.addObject("data",result);
 			view.addObject("databaseid", dbid) ;
 	    	view.addObject("dirid", dirid) ;
 			return view;
 		}
    	//当连接成功时，直接返回显示的表跳转到添加页面
    	view1.addObject("tables", DatabaseMetaDataHandler.getTables(database,null));
    	view1.addObject("databaseid", dbid) ;
    	view1.addObject("dirid", dirid) ;
    	return view1;
    }
                             
    @RequestMapping(value ="/{dbid}/{tabledirid}/tableaddo",name="tableaddo" , type="dm",subtype="database")
    public ModelAndView tableaddo(HttpServletRequest request ,@PathVariable String orgi,@PathVariable String dbid,@PathVariable String tabledirid,@Valid String[] tables) {
		Database database = (Database) super.getService().getIObjectByPK(Database.class, dbid) ;
	  	ResponseData responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(tabledirid).append(".html?msgcode=S_DM_80010024").toString()) ;
	  	boolean flag = true;
	  	int count = 0;
	  	if (null != tables && tables.length > 0){
	  		for (int i = 0; i < tables.length; i++) {
	  			String tableName = tables[i];
	  			count = super.getService().getCountByCriteria(
	  					DetachedCriteria.forClass(TableTask.class).
	  					add(Restrictions.eq("orgi",orgi)).
	  					add(Restrictions.eq("dbid", dbid)).
	  					add(Restrictions.eq("tabledirid", tabledirid)).
	  					add(Restrictions.eq("tablename",tableName)));
	  			if (count > 0) {
	  				flag = false;
	  				break;
	  			}
	  		}
	  			if(flag){
	  				TableTask tabletask = null;
	  				for(String table : tables){
		  				tabletask = new TableTask();
		  				//当前记录没有被添加过，进行正常添加
		  				tabletask.setTablename(table);
		  				tabletask.setDatabase(database);
		  				tabletask.setOrgi(orgi);
		  				tabletask.setDbid(dbid);
		  				tabletask.setTabledirid(tabledirid);
		  				tabletask.setName(table);
		  				tabletask.setUpdatetime(new Date());
		  				Throwable ex = null ;
						try {
							flag = updateMeta(tabletask ,responseData, orgi);
						} catch (Throwable e) {
							ex = e ;
						}
						if(flag){
							tabletask.setCreatetime(new Date());
							super.getService().saveIObject(tabletask) ;
						}else{
							responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(tabledirid).append(".html?msgcode=E_DM_80010024").toString() , ex) ;
						}
	  				}
	  			}else{
	  				responseData = new ResponseData(new StringBuffer().
  							append("redirect:/{orgi}/dm/database/").
  							append(dbid).append("/tabledir/").
  							append(tabledirid).append(".html?msgcode=E_DM_80010024").toString());
	  			}
	  		}
		ModelAndView modelView = request(responseData , orgi);
		
        return modelView ;
    }
    /**
     * 移动数据表
     * @param request
     * @param orgi
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/{dbid}/{tabledirid}/tablesmove" , name="tablesmove" , type="dm",subtype="database")
    public ModelAndView tablesmove(HttpServletRequest request , @PathVariable String orgi, @PathVariable String dbid) throws Exception{ 
    	ModelAndView view = request(super.createPageResponse("/pages/datamodel/database/tablesmove") , orgi);
    	List<TableTask> tableDirList = super.getService().findAllByCriteria(DetachedCriteria.forClass(TableDir.class).add(Restrictions.eq("databaseid",dbid)).add(Restrictions.eq("orgi",orgi)));
    	view.addObject("tableDirList",tableDirList);
    	return view ;
    }
    
    @RequestMapping(value="/{dbid}/{tabledirid}/tablemoveList" , name="tablemoveList" , type="dm",subtype="database")
    public ModelAndView tablemoveList(HttpServletRequest request , @PathVariable String orgi,@Valid String[] tableids,@PathVariable String dbid, @PathVariable String tabledirid,@Valid String dirid) throws Exception{  
    	ResponseData response = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(dirid).append(".html?msgcode=S_DM_80010035").toString()) ;
    	TableTask table = null;
    	Boolean bool =true;
    	for (int i = 0; i < tableids.length; i++) {
    		table = (TableTask)super.getService().getIObjectByPK(TableTask.class, tableids[i]);
    		List<TableTask> tabs = super.getService().findAllByCriteria(DetachedCriteria.forClass(TableTask.class)
    				.add(Restrictions.eq("name", table.getName())).add(Restrictions.eq("orgi", orgi))
    				.add(Restrictions.eq("tabledirid", dirid)));
    		if(tabs!=null && tabs.size()>0){
    			response = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(dirid).append(".html?msgcode=E_DM_80010040").toString()) ;
    			bool = false;
    			break;
    		}
    	}
    	if(bool){
    		for (int i = 0; i < tableids.length; i++) {
    			table = (TableTask)super.getService().getIObjectByPK(TableTask.class, tableids[i]);
    			if(table.getTabledirid().equals(dirid)){
    				response = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(dirid).append(".html?msgcode=E_DM_80010040").toString()) ;
    				break;
    			}
    			table.setTabledirid(dirid);
    			super.getService().saveOrUpdateIObject(table);
    			
    		}
    	}
    	ModelAndView view = request(response,orgi);
    	return view ;
    }

    /**
     * 删除数据表
     * @param request
     * @param orgid
     * @param tableid
     * @param data
     * @return
     */
    @RequestMapping(value = "/{dbid}/tabledel/{tabledirid}/{tableid}",name="tabledel" , type="dm",subtype="database")
    public ModelAndView tabledel(HttpServletRequest request ,@PathVariable String orgi, @PathVariable String tableid, @PathVariable String tabledirid,@PathVariable String dbid) {
		TableTask table = (TableTask) super.getService().getIObjectByPK(TableTask.class, tableid) ;
		ResponseData responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(tabledirid).append(".html").toString()) ; 
		TableTask tt = null;
		int count = super.getService().getCountByCriteria(DetachedCriteria.forClass(CubeMeasure.class).add(Restrictions.eq("tablename", table.getName())).add(Restrictions.eq("orgi", orgi)));
		int count1 = super.getService().getCountByCriteria(DetachedCriteria.forClass(CubeMetadata.class).add(Restrictions.eq("tb.id", tableid)).add(Restrictions.eq("orgi", orgi)));
		int count2 = super.getService().getCountByCriteria(DetachedCriteria.forClass(CubeLevel.class).add(Restrictions.eq("tablename", table.getName())).add(Restrictions.eq("orgi", orgi)));
		
		if(count>0||count1>0||count2>0){
			responseData.setPage(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(tabledirid).append(".html?msgcode=E_DM_80010022").toString());
		}else{
			tt = new TableTask();
			tt.setId(tableid);
			super.getService().deleteIObject(tt);
			responseData.setPage(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(tabledirid).append(".html?msgcode=S_DM_80010023").toString());
		}
		ModelAndView modelView = request(responseData , orgi);
        return modelView ;
    }
    /**
     * 批量删除表
     * @param request
     * @param orgi
     * @param tableids
     * @param table
     * @param tabledirid
     * @param dbid
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/{dbid}/{tabledirid}/tabledellist" , name="tabledellist" , type="dm",subtype="database")
    public ModelAndView tabledellist(HttpServletRequest request , @PathVariable String orgi,@Valid String[] tableids,@PathVariable String tabledirid, @PathVariable String dbid) throws Exception{  
    	ResponseData response = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(tabledirid).append(".html?msgcode=S_DM_80010032").toString()) ;
    	boolean flag = true;
    	for(int i=0;i<tableids.length;i++){
    		TableTask table = (TableTask) super.getService().getIObjectByPK(TableTask.class, tableids[i]) ;
    		int count = super.getService().getCountByCriteria(DetachedCriteria.forClass(CubeMeasure.class).add(Restrictions.eq("tablename", table.getName())).add(Restrictions.eq("orgi", orgi)));
    		int count1 = super.getService().getCountByCriteria(DetachedCriteria.forClass(CubeMetadata.class).add(Restrictions.eq("tb.id", tableids[i])).add(Restrictions.eq("orgi", orgi)));
    		int count2 = super.getService().getCountByCriteria(DetachedCriteria.forClass(CubeLevel.class).add(Restrictions.eq("tablename", table.getName())).add(Restrictions.eq("orgi", orgi)));
    		if(count>0||count1>0||count2>0){
    			flag = false;
    			break;
    		}
    	}
    	if(flag){
    		TableTask tt = null;
    		for (int i = 0; i < tableids.length; i++) {
    			tt = new TableTask();
    			tt.setId(tableids[i]);
    			super.getService().deleteIObject(tt);
			}
    	}else{
    		response.setPage(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/tabledir/").append(tabledirid).append(".html?msgcode=E_DM_80010023").toString());
    		
    	}
    	ModelAndView modelView = request(response, orgi);
        return modelView ;
    }
    /**
     * 还原已删除字段
     * @param request
     * @param orgi
     * @param id
     * @return
     */
	@RequestMapping(value ="/propertyupdate/{id}",name="propertyupdate" , type="dm",subtype="database")
    public ModelAndView propertyupdate(HttpServletRequest request ,@PathVariable String orgi, @PathVariable String id) {
		TableTask table = (TableTask) super.getService().getIObjectByPK(TableTask.class, id) ;
		HttpSession session = request.getSession();
    	TableTask tableTask = (TableTask) session.getAttribute("cuTable");
    	ResponseData responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(tableTask.getDbid()).append("/").append(tableTask.getTabledirid()).append("/table/").append(tableTask.getId()).append(".html").toString()); 
    	Throwable ex = null ;
		try {
			updateMeta(table,responseData,orgi);
		} catch (Throwable e) {
			ex = e ;
		}
    	
		ModelAndView view = request(responseData,orgi);
    	return view ;
    }
    /**
     * 删除表字段
     * @param request
     * @param orgid
     * @param tableid
     * @param data
     * @return
     */
    @RequestMapping(value = "/{dbid}/{tabledirid}/{dbtableid}/tablepropertydel/{id}",name="tablepropertydel" , type="dm",subtype="database")
    public ModelAndView tablepropertydel(HttpServletRequest request ,@PathVariable String orgi,@PathVariable String dbtableid,@PathVariable String id,@PathVariable String dbid,@PathVariable String tabledirid) {
    	TableProperties properties = (TableProperties) super.getService().getIObjectByPK(TableProperties.class, id) ;
    	ResponseData responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/").append(tabledirid).append("/table/").append(dbtableid).append(".html?msgcode=S_DM_80010033").toString());
    	int count = super.getService().getCountByCriteria(DetachedCriteria.forClass(CubeLevel.class).add(Restrictions.eq("tableproperty.id", id)).add(Restrictions.eq("orgi", orgi)));
    	int count1 = super.getService().getCountByCriteria(DetachedCriteria.forClass(Dimension.class).add(Restrictions.eq("fkfield", id)).add(Restrictions.eq("orgi", orgi)));
    	if(count>0||count1>0){
    		responseData.setPage(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(dbid).append("/").append(tabledirid).append("/table/").append(dbtableid).append(".html?msgcode=E_DM_80010033").toString());
    	}else{
    		super.getService().deleteIObject(properties);
    	}
		
		ModelAndView view = request(responseData, orgi);
	    return view;
    }
    
    /**
     * 删除表字段
     * @param request
     * @param orgid
     * @param tableid
     * @param data
     * @return
     */
    @RequestMapping(value = "/{dbid}/{tabledirid}/{dbtableid}/tablepropertypk/{id}",name="tablepropertypk" , type="dm",subtype="database")
    public ModelAndView tablepropertypk(HttpServletRequest request ,@PathVariable String orgi,@PathVariable String dbtableid,@PathVariable String id,@PathVariable String dbid,@PathVariable String tabledirid) {
    	TableProperties properties = (TableProperties) super.getService().getIObjectByPK(TableProperties.class, id) ;
    	ResponseData responseData = new ResponseData("/pages/public/success");
    	if(request.getParameter("v")!=null){
    		if(request.getParameter("v").equals("true")){
    			properties.setPk(true) ;
    		}else{
    			properties.setPk(false) ;
    		}
    		super.getService().updateIObject(properties) ;
    	}
	    return request(responseData, orgi);
    }
    /**
     * 批量删除表字段
     * @param request
     * @param orgi
     * @param dbtableid
     * @param id
     * @param dbid
     * @param tabledirid
     * @return
     */
    @RequestMapping(value = "/tablepropertydellist", name="tablepropertydellist" , type="dm",subtype="database")
    public ModelAndView tablepropertydellist(HttpServletRequest request ,@PathVariable String orgi,@Valid String[] propertyids) {
    	HttpSession session = request.getSession();
    	TableTask tableTask = (TableTask) session.getAttribute("cuTable");
    	
    	ResponseData responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(tableTask.getDbid()).append("/").append(tableTask.getTabledirid()).append("/table/").append(tableTask.getId()).append(".html?msgcode=S_DM_80010034").toString()); 
    	TableProperties tp = null;
    	for (int i = 0; i < propertyids.length; i++) {
    		int count = super.getService().getCountByCriteria(DetachedCriteria.forClass(CubeLevel.class).add(Restrictions.eq("tableproperty.id", propertyids[i])).add(Restrictions.eq("orgi", orgi)));
        	int count1 = super.getService().getCountByCriteria(DetachedCriteria.forClass(Dimension.class).add(Restrictions.eq("fkfield", propertyids[i])).add(Restrictions.eq("orgi", orgi)));
        	if(count>0||count1>0){
        		responseData = new ResponseData(new StringBuffer().append("redirect:/{orgi}/dm/database/").append(tableTask.getDbid()).append("/").append(tableTask.getTabledirid()).append("/table/").append(tableTask.getId()).append(".html?msgcode=E_DM_80010033").toString()); 
        	}else{
	        	tp = new TableProperties();
	    		tp.setId(propertyids[i]);
				super.getService().deleteIObject(tp);
        	}
		}
		ModelAndView view = request(responseData, orgi);
	    return view;
    }
    
    /**
     * 导入sql查询(数据查询的SQL到元数据)
     * @param request
     * @param orgi
     * @param dbid
     * @param dirid
     * @return
     */
    @RequestMapping(value = "/{dbid}/sqladd/{dirid}",name="sqladd" , type="dm",subtype="database")
    public ModelAndView sqladd(HttpServletRequest request ,@PathVariable String orgi, @PathVariable String dbid, @PathVariable String dirid) {
    	ModelAndView view =  request(new ResponseData("/pages/datamodel/database/sqladd") , orgi) ;
    	view.addObject("databaseid", dbid) ;
    	view.addObject("dirid", dirid) ;
    	return view ;
    }
  
    @RequestMapping(value = "/sqladdo",method=RequestMethod.POST,name="sqladdo" , type="dm",subtype="database")
    public ModelAndView sqladdo(HttpServletRequest request ,@PathVariable String orgi, @Valid String dbid, @Valid String tabledirid ,@Valid String sql,@Valid String name) throws Exception {
    	Database database = (Database) super.getService().getIObjectByPK(Database.class, dbid) ;
    	ResponseData response = new ResponseData("/pages/public/success",orgi) ;
    	String result = null;
		TableTask tableTask = null;
		boolean flag = true;
		response = new ResponseData(
				"redirect:/{orgi}/dm/database/"+dbid+"/tabledir/"+tabledirid+".html?msgcode=S_DM_80010030");
		int count = super.getService().getCountByCriteria(
				DetachedCriteria.forClass(TableTask.class).
				add(Restrictions.eq("orgi",orgi)).
				add(Restrictions.eq("dbid", dbid)).
				add(Restrictions.eq("tabledirid",tabledirid)).
				add(Restrictions.eq("tablename",name)));
		ModelAndView view = request(response,orgi) ;
		if(count==0){
			tableTask = new TableTask() ;
			tableTask.setDatabase(database) ;
			tableTask.setDbid(dbid);
			tableTask.setTabledirid(tabledirid);
			tableTask.setOrgi(orgi) ;
			tableTask.setName(name);
			tableTask.setDatasql(sql) ;
			tableTask.setTabletype("2") ;
			
			if(!parseDefaultParam(request , orgi , tableTask)){
				Throwable ex = null ;
				try {
					RivuTableMetaData returnRe = getTableTaskMetadata(tableTask ,response, orgi);
		 			if (null == returnRe) {
		 				response = new ResponseData(
		 						"redirect:/{orgi}/dm/database/"+dbid+"/tabledir/"+tabledirid+".html?msgcode=E_DM_80010038");
		 		        return  request(response, orgi);
		 			}
					flag = updateMeta(tableTask ,response , orgi);
					if(flag){
						tableTask.setTablename(name) ;//tablename
						super.getService().saveIObject(tableTask) ;
					}else{
						 response = new ResponseData(
			 						"redirect:/{orgi}/dm/database/"+dbid+"/tabledir/"+tabledirid+".html?msgcode=E_DM_80010037"); 
					}
				} catch (Throwable e) {
					e.printStackTrace();
					ex = e ;
					String erMs = LocalTools.getMessage("E_DM_80010034");
					result = erMs+ex;
//					response = new ResponseData(
//							"redirect:/{orgi}/dm/database/"+dbid+"/tabledir/"+tabledirid+".html");
					response = new ResponseData(
							"redirect:/{orgi}/dm/database/"+dbid+"/tabledir/"+tabledirid+".html?ation_message="+URLEncoder.encode(result,"UTF-8"));
					view = request(response,orgi) ;
					return view;
				}
			}else{
				tableTask.setTablename(name) ;//tablename
				super.getService().saveIObject(tableTask) ;
				view.addObject("table",  tableTask) ;
				if(tableTask.getPreviewtemplet()!=null){
					view.addObject("param" , tableTask.getPreviewtemplet()!=null && tableTask.getPreviewtemplet().length()>0 ) ;
				}
				return view ;
			}
			
		}else{//已经存在，添加失败 
			 response = new ResponseData(
						"redirect:/{orgi}/dm/database/"+dbid+"/tabledir/"+tabledirid+".html?msgcode=E_DM_80010030");
			 view.addObject("params", tableTask) ;
		}
		view = request(response,orgi) ;
		view.addObject("ation_message",result);
		return view;
    }
    
    /**
     * 参数解析
     * @param request
     * @param orgi
     * @param tableTask
     * @return
     */
    private boolean parseDefaultParam(HttpServletRequest request , String orgi  , TableTask tableTask){
    	DefaultParam defaultParam = new DefaultParam();
    	defaultParam.setFormat("yyyy-MM-dd") ;
    	parsePattern(tableTask.getDatasql(), defaultParam) ;
    	if(defaultParam.getValues()!=null && defaultParam.getValues().size()>0){
    		tableTask.setPreviewtemplet(JSON.toJSONString(defaultParam)) ;
    	}else{
    		tableTask.setPreviewtemplet(null) ;
    	}
    	return defaultParam.getValues().size()>0 ;
    }
    /**
     * 递归解析 内容 ， T已内置为参数，无需设置，如 T+1 , T - 1
     * @param sql
     * @param defaultParam
     */
    private void parsePattern(String sql , DefaultParam defaultParam ){
    	Pattern pattern = Pattern.compile("\\$\\{([\\S\\s]*?)\\}") ;
    	Matcher matcher = pattern.matcher(sql) ;
    	defaultParam.setFormat(RivuDataContext.DEFAULT_DATE_FORMAT) ;
    	if(matcher.find() && matcher.groupCount()>=1){
    		String param = matcher.group(1);
			if(param.length() > 0 && param.matches("[a-zA-Z_0-9+-]{1,}")){
				defaultParam.getValues().add(new DefaultValue(param , "" ,defaultParam.getFormat()));
			}
			parsePattern(sql.substring(matcher.end()) , defaultParam) ;
    	}
    }
    /**
     * 修改sql查询语句
     * @param request
     * @param orgi
     * @param dbid
     * @param dirid
     * @return
     */
    @RequestMapping(value = "/{dbid}/sqledit/{dirid}/{tableid}",name="sqledit" , type="dm",subtype="database")
    public ModelAndView sqledit(HttpServletRequest request ,@PathVariable String orgi, @PathVariable String dbid, @PathVariable String dirid,@Valid TableTask tableTask,@PathVariable String tableid) {
    	ModelAndView view =  request(new ResponseData("/pages/datamodel/database/sqledit") , orgi) ;
    	view.addObject("databaseid", dbid) ;
    	view.addObject("dirid", dirid) ;
    	view.addObject("table", super.getService().getIObjectByPK(TableTask.class, tableid)) ;
    	return view ;
    }
    @RequestMapping(value = "/sqledito",method = RequestMethod.POST,name="sqledito" , type="dm",subtype="database")
    public ModelAndView sqledito(HttpServletRequest request ,@PathVariable String orgi,@Valid TableTask table,@Valid String tabledirid,@Valid String databaseid,@Valid String isproperty) throws Exception{
		TableTask tableTask = (TableTask) super.getService().getIObjectByPK(TableTask.class, table.getId()) ;
		boolean flag = true;
		String result = null;
		ResponseData responseData = new ResponseData(
				"redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?msgcode=S_DM_80010031");
		int count = super.getService().getCountByCriteria(
				DetachedCriteria.forClass(TableTask.class).
				add(Restrictions.eq("orgi",orgi)).
				add(Restrictions.eq("dbid", tableTask.getDbid())).
				add(Restrictions.eq("tabledirid", tableTask.getTabledirid())).
				add(Restrictions.eq("tablename",table.getName())));
 		if(!tableTask.getName().equals(table.getName())&&count>0){
 			responseData = new ResponseData(
 					"redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?msgcode=E_DM_80010031");
	 	}else{
	 		
	 		tableTask.setName(table.getName()) ;
			tableTask.setDatasql(table.getDatasql()) ;
			tableTask.setTableproperty(tableTask.getTableproperty());
			tableTask.setTaskname(table.getName());
			tableTask.setTablename(table.getName());
			
	 		//1代表已选中更新表字段checkbox，进行字段更新操作
		 	if(isproperty!=null && isproperty.equals("1")){

			 		try{
			 			flag = updateMeta(tableTask ,responseData, orgi);
 			 			if(flag){
							for(TableProperties property : tableTask.getTableproperty()){
								int propercount = super.getService().getCountByCriteria(DetachedCriteria.forClass(TableProperties.class).add(Restrictions.eq("dbtableid",  tableTask.getId())).add(Restrictions.eq("fieldname",  property.getFieldname())).add(Restrictions.eq("name", property.getName())));
								if(propercount<=0){
									property.setDbtableid(tableTask.getId());
									super.getService().saveIObject(property);
								}
								
							}
							parseDefaultParam(request, orgi , tableTask);
							super.getService().updateIObject(tableTask);
						}else{
							 responseData = new ResponseData(
				 						"redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?msgcode=E_DM_80010037"); 
						}
				 		
			 			super.getService().updateIObject(tableTask);
			 			ModelAndView view = request(responseData , orgi);
			 			view.addObject("table", tableTask) ;
			 			view.addObject("param" , tableTask.getPreviewtemplet()!=null && tableTask.getPreviewtemplet().length()>0 ) ;
			 			return view ;
				 		
			 		} catch (Throwable e) {
			 			e.printStackTrace();
						String erMs = LocalTools.getMessage("E_DM_80010034");
						result = erMs+e;
						responseData = new ResponseData(
								"redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html");
					}
		    	
 			}else{
 				
 				//修改sql语句，不进行更新字段
		 		Throwable ex = null ;
		 		try{
		 			/**
		 			 * 保存参数
		 			 */
		 			RivuTableMetaData tableMetaData = getTableTaskMetadata(tableTask, responseData , orgi);
		 			parseDefaultParam(request, orgi , tableTask);
		 			super.getService().updateIObject(tableTask);
		 		} catch (Throwable e) {
					ex = e ;
					e.printStackTrace();
					String erMs = LocalTools.getMessage("E_DM_80010034");
					result = erMs+ex;
					responseData = new ResponseData(
							"redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?ation_message="+URLEncoder.encode(result,"UTF-8"));
				}
 			}
 		}
 		
		ModelAndView view = request(responseData , orgi);
		view.addObject("ation_message",result);
        return  view;
    }
    
    /**
     * 设置sql查询语句的默认参数
     * @param request
     * @param orgi
     * @param dbid
     * @param dirid
     * @return
     */
    @RequestMapping(value = "/{dbid}/sqlparam/{dirid}/{tableid}",name="sqlparam" , type="dm",subtype="database")
    public ModelAndView sqlparam(HttpServletRequest request ,@PathVariable String orgi, @PathVariable String dbid, @PathVariable String dirid,@Valid TableTask tableTask,@PathVariable String tableid) {
    	ModelAndView view =  request(new ResponseData("/pages/datamodel/database/sqlparam") , orgi) ;
    	view.addObject("databaseid", dbid) ;
    	view.addObject("dirid", dirid) ;
    	tableTask =  (TableTask) super.getService().getIObjectByPK(TableTask.class, tableid) ;
    	view.addObject("table",tableTask) ;
    	if(tableTask.getPreviewtemplet()!=null && tableTask.getPreviewtemplet().length()>0){
    		view.addObject("params" , JSON.parseObject(tableTask.getPreviewtemplet(), DefaultParam.class)) ;
    	}
    	return view ;
    }
    
    
    @RequestMapping(value = "/{dbid}/sqlexcute/{dirid}/{tableid}",name="sqlexcute" , type="dm",subtype="database")
    public ModelAndView sqlexcute(HttpServletRequest request ,@PathVariable String orgi, @PathVariable String dbid, @PathVariable String dirid,@Valid TableTask tableTask,@PathVariable String tableid) {
    	ModelAndView view =  request(new ResponseData("/pages/datamodel/database/sqlexcute") , orgi) ;
    	view.addObject("databaseid", dbid) ;
    	view.addObject("dirid", dirid) ;
    	tableTask =  (TableTask) super.getService().getIObjectByPK(TableTask.class, tableid) ;
    	view.addObject("table",tableTask) ;
    	return view ;
    }
    
    
    @RequestMapping(value = "/sqlexcutedo",name="sqlexcutedo" , type="dm",subtype="database")
    public ModelAndView sqlexcutedo(HttpServletRequest request ,@PathVariable String orgi,@Valid String tableid,@Valid String tabledirid,@Valid String databaseid,@Valid String wherestr) {
    	ResponseData responseData = new ResponseData("redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?msgcode=S_DM_80010041");
    	
    	TableTask tableTask =  (TableTask) super.getService().getIObjectByPK(TableTask.class, tableid) ;
    	if(org.apache.commons.lang.StringUtils.isBlank(wherestr)){
    		responseData.setPage("redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?msgcode=E_DM_80010041");
    	}else{
    		try {
				Database db = (Database)super.getService().getIObjectByPK(Database.class, databaseid);
				H2Helper.executeSQLOpenException("DELETE "+tableTask.getName()+" WHERE "+wherestr , db) ;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				String result ="删除失败："+e.getMessage();
				
				try {
					responseData.setPage("redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?ation_message="+result);
				} catch (Exception e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}	
    	}
    	ModelAndView view =  request(responseData , orgi) ;
    	return view ;
    }
    
    
    /**
     * 修改默认参数
     * @param request
     * @param orgi
     * @param table
     * @param tabledirid
     * @param databaseid
     * @param isproperty
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/sqlparamdo",method = RequestMethod.POST,name="sqlparamdo" , type="dm",subtype="database")
    public ModelAndView sqlparamdo(HttpServletRequest request ,@PathVariable String orgi,@Valid TableTask table,@Valid String tabledirid,@Valid String databaseid,@Valid String isproperty) throws Exception{
		TableTask tableTask = (TableTask) super.getService().getIObjectByPK(TableTask.class, table.getId()) ;
		
		if(tableTask.getPreviewtemplet()!=null && tableTask.getPreviewtemplet().length()>0){
			DefaultParam defaultParam = JSON.parseObject(tableTask.getPreviewtemplet(), DefaultParam.class) ;
			defaultParam.setUserdate(request.getParameter("userdate")!=null) ;
			defaultParam.setFormat(request.getParameter("format")) ;
			if(defaultParam.getValues().size()>0){
				for(DefaultValue value : defaultParam.getValues()){
					value.setValue(request.getParameter(value.getName())) ;
				}
			}
			tableTask.setPreviewtemplet(JSON.toJSONString(defaultParam)) ;
		}
		
		boolean flag = true;
		String result = null;
		ResponseData responseData = new ResponseData(
				"redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?msgcode=S_DM_80010031");
		//1代表已选中更新表字段checkbox，进行字段更新操作
    	Throwable ex = null ;
 		try{
 			if(tableTask.getTableproperty()==null || tableTask.getTableproperty().size()==0){
 				flag = updateMeta(tableTask ,responseData, orgi);
	 			if(flag){
					for(TableProperties property : tableTask.getTableproperty()){
						property.setDbtableid(tableTask.getId());
						super.getService().saveIObject(property);
					}
					super.getService().updateIObject(tableTask);
				}else{
					super.getService().updateIObject(tableTask);
					 responseData = new ResponseData(
		 						"redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html?msgcode=E_DM_80010037"); 
				}
 			}else{
 				super.getService().updateIObject(tableTask);
 			}
 		} catch (Throwable e) {
 			ex = e ;
			String erMs = LocalTools.getMessage("E_DM_80010034");
			result = erMs+ex;
			responseData = new ResponseData(
					"redirect:/{orgi}/dm/database/"+databaseid+"/tabledir/"+tabledirid+".html");
		}
 		
		ModelAndView view = request(responseData , orgi);
		view.addObject("ation_message",result);
        return  view;
    }
    
    
    private RivuTableMetaData getTableTaskMetadata(TableTask data , ResponseData response , String orgi) throws Exception{
    	RivuTableMetaData tableMetaData = null;
    	try {
			if(data.getTabletype().equals("1")){
				tableMetaData = DatabaseMetaDataHandler.getTable(data.getDatabase() , data.getTablename());
			}else if(data.getTabletype().equals("2")){
				//判断sql语句是否是查询操作
				if(data.getDatasql().toLowerCase().indexOf("select ")<0 
						|| data.getDatasql().toLowerCase().indexOf(" update ")>=0 || data.getDatasql().startsWith("update ") || data.getDatasql().endsWith(" update") 
						|| data.getDatasql().toLowerCase().indexOf(" delete ")>=0 || data.getDatasql().startsWith("delete ") || data.getDatasql().endsWith(" delete")
						|| data.getDatasql().toLowerCase().indexOf(" alter ")>=0 || data.getDatasql().startsWith("alter ") || data.getDatasql().endsWith(" alter")
						|| data.getDatasql().toLowerCase().indexOf(" insert ")>=0 || data.getDatasql().startsWith("insert ") || data.getDatasql().endsWith(" insert")
						|| data.getDatasql().toLowerCase().indexOf(" drop ")>=0 || data.getDatasql().startsWith("drop ") || data.getDatasql().endsWith(" drop")
						|| data.getDatasql().toLowerCase().indexOf(" create ")>=0 || data.getDatasql().startsWith("create ") || data.getDatasql().endsWith(" create")
						|| data.getDatasql().toLowerCase().indexOf(" truncate ")>=0 || data.getDatasql().startsWith("truncate ") || data.getDatasql().endsWith(" truncate")
				){
					return null ;
				}else{
					tableMetaData = DatabaseMetaDataHandler.getSQL(data ,data.getTaskname(),data.getDatabase() , data.getDatasql());
					if(data.getTablename()==null){
						data.setTablename("sql") ;
					}
				}
			}
    	}catch(Exception ex){
    		throw ex ;
    	}
    	return tableMetaData ;
    }
    /**
	 * 
	 * @return
     * @throws Throwable 
	 * @throws IOException
	 */
    public  boolean updateMeta(TableTask data , ResponseData response , String orgi) throws Throwable{
    	boolean flag = true;
		try {
			RivuTableMetaData tableMetaData = getTableTaskMetadata(data, response , orgi);
			TableProperties tablePorperties = null ;
		    if(data.getTableproperty()==null)
		    {
				data.setTableproperty(new HashSet<TableProperties>()) ;
				if(tableMetaData!=null){
					tableMetaData.setName(data.getTablename()) ;
					response.setData(tableMetaData) ;
					for(RivuColumnMetadata colum : tableMetaData.getColumnMetadatas()){
						tablePorperties = new TableProperties(colum.getName() , colum.getTypeName() , colum.getColumnSize() , data.getTablename()) ;
						tablePorperties.setOrgi(orgi) ;
						data.getTableproperty().add(tablePorperties) ;
					}
				}else{//表字段获取失败，表导入失败
					flag = false;
				}
		    }
		    else {
		    	Map  metaTableProps = new HashMap();
		    	for(TableProperties table: data.getTableproperty())
		    	{
		    		//将已经存在的表字段属性添加到map中 增加查询效率 key 表字段名  value 表属性对象
		    		for(RivuColumnMetadata colum : tableMetaData.getColumnMetadatas()){
						if(table.getFieldname().equals(colum.getName())){
							table.setDatatypename(colum.getTypeName());
							table.setDatatypecode(colum.getColumnSize());
							break;
						}
					}
		    		metaTableProps.put(table.getName(), table);
		    	}
		    	for(RivuColumnMetadata colum : tableMetaData.getColumnMetadatas()){
					//如果在数据源 库中有字段更新  则添加进来
					if(!metaTableProps.containsKey(colum.getName()))
					{
						tablePorperties= new TableProperties(colum.getName() , colum.getTypeName() , colum.getColumnSize() , data.getTablename()) ;
						tablePorperties.setOrgi(orgi) ;
						tablePorperties.setDbtableid(data.getId());
						super.getService().saveIObject(tablePorperties);
					}
				}
		    	
		    }
		} catch (Throwable e) {//导入出现异常，导入失败！
			e.printStackTrace();
			flag = false;
			throw e ;
		}
		return flag;
	}
}